--1--
/*with Aux as
	(	Select CodigoAlumno, cast(AVG(NotaFinal) as int) as 'Promedio'
		from Cursa
		Group by CodigoAlumno
		Having cast(AVG(NotaFinal) as int) >= 51
	)
,
Aux2 as
	(	Select C.IdMateria, C.CodigoAlumno, Ca.Nombre
		from Cursa as C
		join Materia as M on C.IdMateria = M.Id 
		join Carrera as Ca on Ca.Id = M.IdCarrera

	)

Select Concat(P.Nombre,P.SegundoNombre,P.ApellidoPaterno,P.ApellidoMaterno) as 'Nombre Completo', Aux2.Nombre as 'Carrera'
from Persona as P
join Aux on Aux.CodigoAlumno = P.Codigo
join Aux2  on Aux2.CodigoAlumno = P.Codigo 

--2--*/

with Mejores as
	(	Select top 2 case 

		when Cursa.NotaFinal < 51 then 'Reprobado'
		else 'Aprobado'
		end as 'A o P'
	
		,M.IdCarrera , Cursa.CodigoAlumno 
		from Cursa
		join Materia as M on M.Id = Cursa.IdMateria 
		--group by M.IdCarrera
		Order by NotaFinal desc
	)
,
Peores as
	(	Select top 2 case 
	when Cursa.NotaFinal < 51 then 'Reprobado'
	else 'Aprobado'
	end as 'A o P'
	, M.IdCarrera, Cursa.CodigoAlumno 
		from Cursa
		join Materia as M on M.Id = Cursa.IdMateria 
		--group by M.IdCarrera
		Order by NotaFinal 
	)
Select *
from Mejores
union all
Select *
from Peores

--3--*/
/*with A as 

	(	Select count(*) as 'Reprobados',idMateria
		from Cursa
		group by IdMateria,NotaFinal
		having NotaFinal <= 51
	)
Select Nombre
from Materia
join A on A.IdMateria = Materia.Id
where A.Reprobados= (select MAX(A.Reprobados)
					from A)

--4--*/
/*with B as

	(	Select min(NotaFinal) as 'NF',CodigoAlumno, IdMateria
		from Cursa
		group by CodigoAlumno,IdMateria
	)
select M.Nombre, B.CodigoAlumno
from Materia as M
join B on B.IdMateria = M.Id

--5--*/

/*with D as
(Select MAX(NotaFinal) as 'Nota final',Semestre
from Cursa
group by Semestre
)

Select C.CodigoAlumno,P.Nombre,P.ApellidoPaterno, D.[Nota final], D.Semestre,M.IdCarrera
from Cursa as C
join Persona as P on C.CodigoAlumno = P.Codigo
join Materia as M on M.Id = C.IdMateria
join D on D.[Nota final] = C.NotaFinal

--6--*/

/*Declare @Pag int
set @Pag =2

Select * 
from Persona
order by ApellidoPaterno
Offset (@Pag * 10) rows fetch next 10 rows only
*/



